USE CourierDatabase
GO
--create login name
CREATE PROCEDURE CreateLoginName
@loginName VARCHAR(15) OUTPUT
AS
BEGIN
	DEClARE @ID INT
	SET @ID = (SELECT MAX(IDEmp) FROM CourierDatabase.dbo.TempTable)
	SET @loginName = 'EMP' + RIGHT('00000' + CONVERT(VARCHAR(6),@ID),6)
END
GO

--call when add employee success, increase ID in IndexIDEmployee
CREATE PROCEDURE IncreaseIDEmployee
AS
BEGIN
	UPDATE CourierDatabase.dbo.TempTable
	SET IDEmp = IDEmp + 1
END
GO

--create courier number
CREATE PROCEDURE CreateCourierName
@courierName VARCHAR(15) OUTPUT
AS
BEGIN
	DEClARE @ID INT
	SET @ID = (SELECT MAX(IDCourier) FROM CourierDatabase.dbo.TempTable)
	SET @courierName = 'CR' + RIGHT('00000' + CONVERT(VARCHAR(6),@ID),6)
END
GO

--call when add employee success, increase ID in IndexIDCourier
CREATE PROCEDURE IncreaseIDCourier

AS
BEGIN
	UPDATE CourierDatabase.dbo.TempTable
	SET IDCourier = IDCourier + 1

END
GO

--check username and password when login
CREATE PROCEDURE checkLogin
@loginName VARCHAR(50),
@password VARCHAR(100),
@haveEmp INT OUTPUT,
@decentralization INT OUTPUT
AS
BEGIN
	SET @haveEmp = (SELECT COUNT(*) FROM CourierDatabase.dbo.Employee
	WHERE Login_Name = @loginName and Login_Password = @password)
	IF(@haveEmp = 1)
	BEGIN
		SET @decentralization = (SELECT Decentralization_Id FROM CourierDatabase.dbo.Employee
		WHERE Login_Name = @loginName)
	END
END
GO

--check security question and answer when forgot password
CREATE PROCEDURE checkForgotPassword
@loginName VARCHAR(50),
@question INT,
@answer VARCHAR(100),
@check INT OUTPUT
AS
BEGIN
	SET @check = (SELECT COUNT(*) FROM CourierDatabase.dbo.Employee 
	WHERE (@loginName = Login_Name AND @question = S_Question AND @answer = S_Answer))
END
GO

CREATE PROCEDURE ChangePassword
@loginName VARCHAR(15),
@newPassword VARCHAR(100)
AS
BEGIN
	UPDATE CourierDatabase.dbo.Employee
	SET Login_Password = @newPassword
	WHERE @loginName = Login_Name
END
GO

CREATE PROCEDURE checkPassword
@loginName VARCHAR(15),
@password VARCHAR(100),
@check INT OUTPUT
AS
BEGIN
	SET @check = (SELECT COUNT(*) FROM CourierDatabase.dbo.Employee
	WHERE @loginName = Login_Name AND @password = Login_Password)
END
GO

CREATE PROCEDURE showNotSentTable
@option INT,
@loginName VARCHAR(15),
@timeCreated DATE
AS
BEGIN
	IF(@option = 0)
	BEGIN
		SELECT Employee.Emp_Id,Lost_Connection.Login_Name,Employee.Emp_Mail,Employee.Time_Created,Lost_Connection.Connected FROM Lost_Connection	
		INNER JOIN Employee ON Lost_Connection.Login_Name = Employee.Login_Name		
	END
	
	ELSE IF(@option = 1)
	BEGIN
		SELECT Employee.Emp_Id,Lost_Connection.Login_Name,Employee.Emp_Mail,Employee.Time_Created,Lost_Connection.Connected FROM Lost_Connection	
		INNER JOIN Employee ON Lost_Connection.Login_Name = Employee.Login_Name		
		WHERE CourierDatabase.dbo.Employee.Login_Name = @loginName				
	END
	
	ELSE IF(@option = 2)
	BEGIN
		SELECT Employee.Emp_Id,Lost_Connection.Login_Name,Employee.Emp_Mail,Employee.Time_Created,Lost_Connection.Connected FROM Lost_Connection	
		INNER JOIN Employee ON Lost_Connection.Login_Name = Employee.Login_Name	
		WHERE CourierDatabase.dbo.Employee.Time_Created = @timeCreated	
	END
	
	ELSE
	BEGIN
		SELECT Employee.Emp_Id,Lost_Connection.Login_Name,Employee.Emp_Mail,Employee.Time_Created,Lost_Connection.Connected FROM Lost_Connection	
		INNER JOIN Employee ON Lost_Connection.Login_Name = Employee.Login_Name	
		WHERE CourierDatabase.dbo.Employee.Login_Name = @loginName	
		AND CourierDatabase.dbo.Employee.Time_Created = @timeCreated	
	END
END
GO

CREATE PROCEDURE AddConnection
@loginName VARCHAR(15)
AS
BEGIN
	INSERT INTO CourierDatabase.dbo.Lost_Connection(Login_Name,Connected) VALUES(@loginName,0)
END
GO

CREATE PROCEDURE GetInfoConnection
@loginName VARCHAR(15)
as
BEGIN
	SELECT Employee.Emp_Mail,Lost_Connection.Login_Name
	FROM Employee INNER JOIN Lost_Connection ON Lost_Connection.Login_Name = Employee.Login_Name
	WHERE Lost_Connection.Login_Name = @loginName
END
GO